package DBFunction;
import Constant.Constant;
import DTO.User;
import Storage.DateManipulation;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.log4j.Logger;

public class UserManager {
	public static Logger logger = Logger.getLogger("BuddyTrackingServer");
	public static void insert(User user) throws SQLException {
		String sql ="INSERT into buddytracking.user (PhoneNumber,Passwords,FirstName,LastName,Address,Email,GroupId) VALUES ('"
		+user.getPhoneNumber()+"','"+user.getPasswords()+"','"+user.getFirstName()+"','"
		+user.getLastName()+"','"+user.getAddress()+"','"+user.getEmail()+"','"+user.getGroupId()+"')" ;
		DB.connectDB.Update(sql);
		logger.info("UserManager.insert: "+user.getFirstName()+" " +user.getLastName()+" inserted.");
	}
	public static void updateWithNewPasswords(User newuser,String oldId) throws SQLException {
		String sql="UPDATE buddytracking.user set PhoneNumber='" + newuser.getPhoneNumber() +"',Passwords='" + newuser.getPasswords()+"',FirstName='" + newuser.getFirstName()+"',LastName='" + newuser.getLastName()+"',Email='" + newuser.getEmail()+"',Address='" + newuser.getAddress()+"'"
		+ " where UserId='" + oldId + "'";
		logger.info("UserManager.updateWithNewPassword: User "+oldId+" Updated.");
		DB.connectDB.Update(sql);
	}
	public static void update(User newuser,String oldId) throws SQLException {
		String sql="UPDATE buddytracking.user set PhoneNumber='" + newuser.getPhoneNumber() +"',FirstName='" + newuser.getFirstName()+"',LastName='" + newuser.getLastName()+"',Email='" + newuser.getEmail()+"',Address='" + newuser.getAddress()+"'"
		+ " where UserId='" + oldId + "'";
		logger.info("UserManager.update: User "+oldId+" Updated.");
		DB.connectDB.Update(sql);
	}
	public static void deleteById(String Id) throws SQLException {
		String sql ="DELETE FROM buddytracking.user WHERE UserId='"+Id+"'" ;
		DB.connectDB.Update(sql);
		logger.info("UserManager.deleteById: User with Id "+Id+" deleted.");
	}
	public static void setInactiveById(String Id) throws SQLException {
		String sql ="UPDATE buddytracking.user SET GroupId='0' WHERE UserId='"+Id+"'" ;
		DB.connectDB.Update(sql);
		logger.info("UserManager.setInactiveById: User with Id "+Id+" inactivated.");
	}
	public static void setInactiveInGroup(String groupName) throws SQLException {
		String sql ="UPDATE buddytracking.user SET GroupId='0' WHERE GroupId=(SELECT GroupId FROM buddytracking.group WHERE GroupName='"+groupName+"')" ;
		DB.connectDB.Update(sql);
		logger.info("UserManager.setInactiveInGroup: User from group "+groupName+" inactivated.");
	}
	public static void changeGroup(String UserId,String newGroupName) throws SQLException {
		String sql ="UPDATE buddytracking.user SET GroupId=(SELECT GroupId FROM buddytracking.group WHERE GroupName='"+newGroupName+"') WHERE UserId='"+UserId+"'" ;
		if(newGroupName.equals("Inactive"))
		{
		String sql2 = "delete FROM buddytracking.position where position.UserId='"+UserId+"'";
		DB.connectDB.Update(sql2);
		}
		DB.connectDB.Update(sql);
		
		logger.info("UserManager.changeGroup: User with Id "+UserId+" moved to group "+newGroupName);
	}
	public static User getUserById(String Id) throws SQLException {
		String sql ="SELECT * FROM buddytracking.user WHERE UserId='"+Id+"'" ;
		User selectedUser = new User();
		ResultSet resultSet = DB.connectDB.ExecuteQuery(sql);
		while (resultSet.next()) {
			selectedUser.setId(resultSet.getString("UserId"));
			selectedUser.setPhoneNumber(resultSet.getString("PhoneNumber"));
			selectedUser.setPasswords(resultSet.getString("Passwords"));
			selectedUser.setFirstName(resultSet.getString("FirstName"));
			selectedUser.setLastName(resultSet.getString("LastName"));
			selectedUser.setEmail(resultSet.getString("Email"));
			selectedUser.setAddress(resultSet.getString("Address"));
		}
		logger.info("UserManager.getUserById: User "+ Id +" selected.");
		return selectedUser;
	}
	public static ArrayList<User> getUserList()throws  SQLException{
		ArrayList<User> userList = new ArrayList();
		String sql ="SELECT * FROM buddytracking.user" ;
		ResultSet resultSet = DB.connectDB.ExecuteQuery(sql);
		User selectedUser = new User();
		logger.info("UserManager.getUserList.Begin");
		while (resultSet.next()) {
			selectedUser.setId(resultSet.getString("UserId"));
			selectedUser.setPhoneNumber(resultSet.getString("PhoneNumber"));
			selectedUser.setPasswords(resultSet.getString("Passwords"));
			selectedUser.setFirstName(resultSet.getString("FirstName"));
			selectedUser.setLastName(resultSet.getString("LastName"));
			selectedUser.setEmail(resultSet.getString("Email"));
			selectedUser.setAddress(resultSet.getString("Address"));
			selectedUser.setGroupId(resultSet.getString("GroupId"));
			userList.add(selectedUser);
			logger.info("UserManager.getUserList: User "+selectedUser.getFirstName()+" "+selectedUser.getLastName()+" added.");
		}
		logger.info("UserManager.getUserList: Total User is "+userList.size());
		logger.info("UserManager.getUserList.End");
		return userList;
	}
	public static ArrayList<User> getUserListInGroup(String groupName)throws  SQLException{
		ArrayList<User> userList = new ArrayList();
		String sql ="SELECT * FROM buddytracking.user WHERE GroupId=(SELECT GroupId FROM buddytracking.group WHERE GroupName='"+groupName+"')" ;
		ResultSet resultSet = DB.connectDB.ExecuteQuery(sql);
		logger.info("UserManager.getUserListInGroup.Begin");
		logger.info("UserManager.getUserListInGroup: Group Name is " +groupName);
		while (resultSet.next()) {
			User selectedUser = new User();
			selectedUser.setId(resultSet.getString("UserId"));
			selectedUser.setPhoneNumber(resultSet.getString("PhoneNumber"));
			selectedUser.setFirstName(resultSet.getString("FirstName"));
			selectedUser.setLastName(resultSet.getString("LastName"));
			selectedUser.setEmail(resultSet.getString("Email"));
			selectedUser.setAddress(resultSet.getString("Address"));
			selectedUser.setIsOnline(resultSet.getString("IsOnline"));
			userList.add(selectedUser);
			logger.info("UserManager.getUserListInGroup: User "+selectedUser.getFirstName()+" "+selectedUser.getLastName()+" added.");
		}
		logger.info("UserManager.getUserList: Total User is "+userList.size());
		logger.info("UserManager.getUserListInGroup.End");
		return userList;
	}
	
	public static ArrayList<User> getUserPositionInGroup(String groupName)throws  SQLException{
		ArrayList<User> userList = new ArrayList();
		String sql ="SELECT *  FROM (SELECT U.UserId, U.PhoneNumber,U.FirstName,U.LastName,U.GroupId,U.IsOnline,P.Longitude,P.Latitude,P.LastUpdate FROM buddytracking.user AS U JOIN buddytracking.position AS P ON U.UserId = P.UserId) AS R WHERE GroupId=(SELECT GroupId FROM buddytracking.group AS G WHERE G.GroupName='"+groupName+"')" ;
		ResultSet resultSet = DB.connectDB.ExecuteQuery(sql);
		logger.info("UserManager.getUserPositionInGroup.Begin");
		logger.info("UserManager.getUserPositionInGroup: Group Name is " +groupName);
		while (resultSet.next()) {
			User selectedUser = new User();
			selectedUser.setId(resultSet.getString("UserId"));
			selectedUser.setPhoneNumber(resultSet.getString("PhoneNumber"));
			selectedUser.setFirstName(resultSet.getString("FirstName"));
			selectedUser.setLastName(resultSet.getString("LastName"));
			selectedUser.setIsOnline(resultSet.getString("IsOnline"));
			selectedUser.setLongitude(resultSet.getString("Longitude"));
			selectedUser.setLatitude(resultSet.getString("Latitude"));
			selectedUser.setLastUpdate(DateManipulation.timeDifference(
					DateManipulation.StringToDate(resultSet
							.getString("LastUpdate")), DateManipulation
							.StringToDate(Constant.getDateTime())));
			userList.add(selectedUser);
		}
		logger.info("UserManager.getUserPositionInGroup: Total User is "+userList.size());
		logger.info("UserManager.getUserPositionInGroup.End");
		return userList;
	}
}
